package trabalho.doo3.web.servlet;

import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 *
 * @author Matheus
 */
@WebServlet(name = "Servlet", urlPatterns = {"/servlet"})
public class Servlet extends HttpServlet {

    private DataSource dataSource;
    private Exception exception;

    @Override
    public void init(ServletConfig config) throws ServletException {
        try {
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("jdbc/postgres");
        } catch (NamingException ex) {
            exception = ex;
        }
    }

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("Conexão invocada");
        resp.setContentType("text/plain;charset=UTF-8");

        String teste = req.getParameter("acao");
        System.out.println(teste);
        if (exception == null) {
            try {
                Long usuario_id = null;
                Connection conn = dataSource.getConnection();
                String opcao = req.getParameter("acao");
                String usuario = req.getParameter("usuario");
                String filme = req.getParameter("filme");
                String senha = req.getParameter("senha");
                String deletar = req.getParameter("deletar");

                if ("login".equals(opcao)) { //login
                    Statement st = conn.createStatement();
                    ResultSet rs = st.executeQuery("select * from usuario where login = '" + usuario + "' and senha = '" + senha + "'");
                    int c = 0;
                    OutputStream outSt = resp.getOutputStream();
                    DataOutputStream dos = new DataOutputStream(outSt);
                    while (rs.next()) {
                        usuario_id = rs.getLong("id");
                        c++;
                    }
                    if (c > 0) {
                        dos.writeUTF(String.valueOf(usuario_id));
                    } else {
                        dos.writeUTF("0");
                    }
                    dos.writeUTF("#Encerra leitura#");
                    dos.close();
                } else if ("filme".equals(opcao)) { //Lista todos os filmes
                    String sql = "select * from filme where id not in(select filme from reserva where usuario =?)";
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.setInt(1, Integer.parseInt(usuario));
                    ResultSet rs = pst.executeQuery();
                    OutputStream outSt = resp.getOutputStream();
                    DataOutputStream dos = new DataOutputStream(outSt);
                    while (rs.next()) {
                        int filme_id = rs.getInt("id");
                        String filme_nome = rs.getString("nome");
                        dos.writeUTF(filme_nome);
                        dos.writeInt(filme_id);
                    }
                    dos.writeUTF("#Encerra leitura#");
                    dos.close();
                } else if ("select_reservas".equals(opcao)) { //Lista todas as reservas
                    String sql = "select * from filme where id in(select filme from reserva where usuario =?)";
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.setInt(1, Integer.parseInt(usuario));
                    ResultSet rs = pst.executeQuery();
                    OutputStream outSt = resp.getOutputStream();
                    DataOutputStream dos = new DataOutputStream(outSt);
                    while (rs.next()) {
                        String nome = rs.getString("nome");
                        dos.writeUTF(nome);
                    }
                    dos.writeUTF("#Encerra leitura#");
                    dos.close();
                } else if ("insert_reserva".equals(opcao)) {
                    if (deletar.equalsIgnoreCase("sim")) {
                        int nomeFilme = procuraFilme(filme);
                        deletarReserva(Integer.parseInt(usuario), nomeFilme);
                    } else {
                        String sql = "insert into reserva (filme,usuario) values (?,?)";
                        PreparedStatement pst = conn.prepareStatement(sql);
                        pst.setInt(1, procuraFilme(filme));
                        pst.setInt(2, Integer.parseInt(usuario));
                        pst.executeUpdate();
                        pst.close();
                    }
                }
                conn.close();

            } catch (SQLException ex) {
                Logger.getLogger(Servlet.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

    private int procuraFilme(String nome) {
        int filme = 0;
        try {
            Connection conexao = dataSource.getConnection();
            String sql = "select * from filme where nome = ?";
            PreparedStatement pst = conexao.prepareStatement(sql);
            pst.setString(1, nome);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                filme = rs.getInt("id");
            }
            rs.close();
            pst.close();
            conexao.close();
        } catch (SQLException ex) {
            Logger.getLogger(Servlet.class.getName()).log(Level.SEVERE, null, ex);
        }
        return filme;
    }

    private void deletarReserva(int nome, int filme) {
        try {
            Connection conexao = dataSource.getConnection();
            String sql = "delete from reserva where filme = ? and usuario = ?";
            PreparedStatement pst = conexao.prepareStatement(sql);
            pst.setInt(1, filme);
            pst.setInt(2, nome);
            pst.executeUpdate();
            pst.close();
            conexao.close();
            System.out.println("Deletado");
        } catch (SQLException ex) {
            Logger.getLogger(Servlet.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}
